package com.example.lsrsbackend.mapper;

import com.example.lsrsbackend.model.Reservation;
import org.apache.ibatis.annotations.*;

import java.sql.Time;
import java.util.Date;
import java.util.List;

@Mapper
public interface ReservationMapper {

    @Select("SELECT * FROM reservations WHERE id = #{id}")
    Reservation selectById(Long id);

    @Select("SELECT * FROM reservations WHERE username = #{username} AND date >= #{startDate} ORDER BY date DESC, start_time DESC")
    List<Reservation> selectByUserAndDate(@Param("username") String username, @Param("startDate") Date startDate);

    @Select("SELECT * FROM reservations WHERE seat_id = #{seatId} AND date = #{date} AND status IN ('RESERVATED', 'CHECKED_IN')")
    List<Reservation> selectActiveReservationsBySeatAndDate(@Param("seatId") Long seatId, @Param("date") Date date);

    @Select("SELECT COUNT(*) FROM reservations WHERE username = #{username} AND date = #{date} AND status IN ('RESERVATED', 'CHECKED_IN')")
    int countByUserAndDate(@Param("username") String username, @Param("date") Date date);

    @Select("SELECT COUNT(*) FROM reservations WHERE seat_id = #{seatId} AND date = #{date} AND status IN ('RESERVATED', 'CHECKED_IN') " +
            "AND ((start_time < #{endTime} AND end_time > #{startTime}))")
    int countTimeConflict(@Param("seatId") Long seatId, @Param("date") Date date,
                          @Param("startTime") Time startTime, @Param("endTime") Time endTime);

    @Insert("INSERT INTO reservations (seat_id, username, date, start_time, end_time, duration_hours, status, created_at, updated_at) " +
            "VALUES (#{seatId}, #{username}, #{date}, #{startTime}, #{endTime}, #{durationHours}, #{status}, NOW(), NOW())")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(Reservation reservation);

    @Update("UPDATE reservations SET status = #{status}, actual_checkin_time = #{actualCheckinTime}, " +
            "actual_checkout_time = #{actualCheckoutTime}, extend_count = #{extendCount}, updated_at = NOW() WHERE id = #{id}")
    int update(Reservation reservation);

    @Select("SELECT * FROM reservations WHERE status = 'RESERVATED' AND created_at < #{timeoutTime}")
    List<Reservation> selectTimeoutReservations(Date timeoutTime);

    @Update("UPDATE reservations SET status = 'EXPIRED', updated_at = NOW() WHERE id = #{id}")
    int updateToExpired(Long id);

    @Select("SELECT * FROM reservations ")
    List<Reservation> selectAll();

    // 在 ReservationMapper 接口中添加
    @Delete("DELETE FROM reservations WHERE id = #{id}")
    int deleteById(Long id);
}